package flights.web;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.*;

import com.google.gson.Gson;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;

import flights.bi.Flight;
import flights.db.DBConn;
import flights.db.DBInitialize;
import flights.db.FlightHandler;

/**
 * Servlet implementation class DBsample
 */
@WebServlet(name = "DBsample", urlPatterns =
{
    "/DBsample"
})
public class DBsample extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DBsample() {
        super();
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		PrintWriter out = response.getWriter();
		response.setContentType( "text/html" ); 
		Connection d = null;
		try {
			
			d = DBConn.createConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String sQueryID = request.getParameter("qid");
		ResultSet rs = null;
		int nRowsAffected = 0;
		int nQueryID = 0;
		if (sQueryID != null)
		{
			try
			{
				nQueryID = Integer.parseInt(sQueryID);
			}
			catch (Exception ex)
			{
				out.println(ex);
			}
		}
		
		switch (nQueryID) {
		case 0:
			 try {
				DBInitialize.Init();
				 
			} catch (SQLException e) {
				String jsonEnvVars = java.lang.System.getenv("VCAP_SERVICES");
				out.println(jsonEnvVars);
				out.println(e.getMessage());
			}
			 catch (JSONException js)
			 {
				 String jsonEnvVars = java.lang.System.getenv("VCAP_SERVICES");
					out.println(jsonEnvVars);
					out.println(js.getMessage());
			 }
			 catch (ClassNotFoundException cse)
			 {
					out.println(cse.getMessage());
			 }
			break;
		case 1:
			 try {
				Gson gson = new Gson();
				List<Flight> flights = FlightHandler.getAllFlights();
				out.print(gson.toJson(flights));
			} catch (Exception e) {
				out.println(e.getMessage());
			}
			break;
		case 2:
			 try {
				rs = d.createStatement().executeQuery("SELECT * FROM flight t where t.arrival_airport = 'Barcelona' and t.depart_date between now() and now() + interval 1 month");
			} catch (SQLException e) {
				out.println(e.getMessage());
			}
			break;
		case 3:
			 try {
				rs = d.createStatement().executeQuery("SELECT sum(cost) FROM ticket b join booking a on (a.booking_id = b.booking_id) join flight_seat c on (c.seat_id = b.seat_id) where a.user_id = 1");
			} catch (SQLException e) {
				out.println(e.getMessage());
			}
			break;
		case 4:
			 try {
				rs = d.createStatement().executeQuery("SELECT DAY(BOOKING_DATE), COUNT(*) FROM booking GROUP BY  DAY(BOOKING_DATE)");
			} catch (SQLException e) {
				out.println(e.getMessage());
			}
			break;
		case 5:
			 try {
				 nRowsAffected = d.createStatement().executeUpdate("UPDATE flight set ");
			} catch (SQLException e) {
				out.println(e.getMessage());
			}
			break;
		case 6:
			 try {
				 nRowsAffected = d.createStatement().executeUpdate("UPDATE flight_seat SET reserved='0' WHERE seat_id='5'");
			} catch (SQLException e) {
				out.println(e.getMessage());
			}
			break;
		case 7:
			 try {
				 nRowsAffected = d.createStatement().executeUpdate("INSERT INTO flight (airline_id, flight_number, depart_airport, arrival_airport, depart_date, arrive_date) VALUES ('1', 'TLV433', 'Paris', 'Ben Gurion', '2013-08-15', '2013-08-16')");
			} catch (SQLException e) {
				out.println(e.getMessage());
			}
			break;
		case 8:
			 try {
				 nRowsAffected = d.createStatement().executeUpdate("INSERT INTO booking (booking_id, user_id, payment_method_id, booking_date) VALUES ('4', '1', '2', '2013-08-15')");
				 nRowsAffected += d.createStatement().executeUpdate("INSERT INTO ticket (booking_id, seat_id, passport_number, first_name, last_name) VALUES ('4', '3', '192949568', 'Guy', 'Lev')");
			} catch (SQLException e) {
				out.println(e.getMessage());
			}
			break;
		case 9:
			 try {
				 nRowsAffected = d.createStatement().executeUpdate("DELETE FROM flight WHERE flight_id='6'");
			} catch (SQLException e) {
				out.println(e.getMessage());
			}
			break;
		case 10:
			 try {
				 nRowsAffected = d.createStatement().executeUpdate("DELETE FROM payment_method WHERE method_id='1'");
			} catch (SQLException e) {
				out.println(e.getMessage());
			}
			break;
		default:
			break;
		}
		
		if (rs != null)
		{
			JSONArray jarr = null;
			try {
				jarr = convert(rs);
			} catch (JSONException e) {
				out.println(e.getMessage());
			}
			catch (SQLException es)
			{
				out.println(es.getMessage());
			}
			out.println(jarr.toString());
		}
		else
		{
			out.println(nRowsAffected);
		}		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		//PrintWriter out = response.getWriter();
		response.setContentType( "text/html" ); 
	}
	
	public static JSONArray convert(ResultSet rs) throws SQLException,
    JSONException {
		JSONArray json = new JSONArray();
		ResultSetMetaData rsmd = rs.getMetaData();
		int numColumns = rsmd.getColumnCount();
		while (rs.next()) {
		
		    JSONObject obj = new JSONObject();
		
		    for (int i = 1; i < numColumns + 1; i++) {
		        String column_name = rsmd.getColumnName(i);
		
		        if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
		            obj.put(column_name, rs.getArray(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
		            obj.put(column_name, rs.getLong(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.REAL) {
		            obj.put(column_name, rs.getFloat(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
		            obj.put(column_name, rs.getBoolean(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
		            obj.put(column_name, rs.getBlob(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
		            obj.put(column_name, rs.getDouble(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
		            obj.put(column_name, rs.getDouble(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) {
		            obj.put(column_name, rs.getInt(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
		            obj.put(column_name, rs.getNString(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
		            obj.put(column_name, rs.getString(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.CHAR) {
		            obj.put(column_name, rs.getString(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.NCHAR) {
		            obj.put(column_name, rs.getNString(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.LONGNVARCHAR) {
		            obj.put(column_name, rs.getNString(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.LONGVARCHAR) {
		            obj.put(column_name, rs.getString(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
		            obj.put(column_name, rs.getByte(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
		            obj.put(column_name, rs.getShort(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
		            obj.put(column_name, rs.getDate(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.TIME) {
		            obj.put(column_name, rs.getTime(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
		            obj.put(column_name, rs.getTimestamp(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.BINARY) {
		            obj.put(column_name, rs.getBytes(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.VARBINARY) {
		            obj.put(column_name, rs.getBytes(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.LONGVARBINARY) {
		            obj.put(column_name, rs.getBinaryStream(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.BIT) {
		            obj.put(column_name, rs.getBoolean(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.CLOB) {
		            obj.put(column_name, rs.getClob(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) {
		            obj.put(column_name, rs.getBigDecimal(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.DECIMAL) {
		            obj.put(column_name, rs.getBigDecimal(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.DATALINK) {
		            obj.put(column_name, rs.getURL(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.REF) {
		            obj.put(column_name, rs.getRef(column_name));
		        } else if (rsmd.getColumnType(i) == java.sql.Types.STRUCT) {
		            obj.put(column_name, rs.getObject(column_name)); // must be a custom mapping consists of a class that implements the interface SQLData and an entry in a java.util.Map object.
		        } else if (rsmd.getColumnType(i) == java.sql.Types.DISTINCT) {
		            obj.put(column_name, rs.getObject(column_name)); // must be a custom mapping consists of a class that implements the interface SQLData and an entry in a java.util.Map object.
		        } else if (rsmd.getColumnType(i) == java.sql.Types.JAVA_OBJECT) {
		            obj.put(column_name, rs.getObject(column_name));
		        } else {
		            obj.put(column_name, rs.getString(i));
		        }
		    }
		
		    json.put(obj);
		}
		
		return json;
	}

}
